import pandas as pd
import pymysql
import json
import logging
from utils.format_util import py_to_java
from common.config.config import *
import psycopg2.pool
import io
#from app import get_conn_from_pool, put_conn_back

logging.basicConfig(level=logging.INFO, format="%(asctime)s - %(name)s - %(levelname)s - %(message)s")

try:
    postgreSQL_pool = psycopg2.pool.SimpleConnectionPool(GP_MIN_CONN, GP_MAX_CONN, user=GP_USER,
                                                         password=GP_PASSWORD,
                                                         host=GP_HOST,
                                                         port=GP_PORT,
                                                         database=GP_DBNAME)
    if (postgreSQL_pool):
        logging.info("Connection pool created successfully")

except (Exception, psycopg2.DatabaseError) as error:
    logging.info("Error while connecting to PostgreSQL", error)


def get_conn_from_pool():
    return postgreSQL_pool.getconn()


def put_conn_back(connection):
    postgreSQL_pool.putconn(connection)



def read_gp(sql):
    df = None
    #conn_pool = get_gp_conn()
    try:
        # Use getconn() to Get Connection from connection pool
        ps_connection = get_conn_from_pool()
        if (ps_connection):
            df = pd.read_sql(sql, ps_connection)
    except (Exception, psycopg2.DatabaseError) as error:
        print("Error while connecting to PostgreSQL", error)
    finally:
        # Use this method to release the connection object and send back to connection pool
        put_conn_back(ps_connection)
        print("Put away a PostgreSQL connection")
    return df


def save_gp(table_name, df):
    logging.info("saving df")
    #conn_pool = get_gp_conn()
    try:
        ps_connection = get_conn_from_pool()
        cursor = ps_connection.cursor()
        sql_drop = "drop table if exists {}".format(table_name)
        cursor.execute(sql_drop)

        dtypes = []
        for dtype in df.dtypes:
            if dtype == float:
                dtypes.append("numeric(30,8)")
            elif dtype == int:
                dtypes.append("BIGINT")
            else:
                dtypes.append("varchar")
        columns = ["\""+column+"\"" + " " + dtype for column, dtype in zip(df.columns, dtypes)]
        sql = "create table {}({})".format(table_name, ",".join(columns))
        cursor.execute(sql)
        data_io = io.StringIO()
        df.to_csv(data_io, sep="|", index=False)
        data_io.seek(0)
        # data_io.readline()  # remove header DO NOT DELETE THIS COMMENT
        copy_cmd = "COPY %s FROM STDIN HEADER DELIMITER '|' CSV" % table_name
        cursor.copy_expert(copy_cmd, data_io)

        ps_connection.commit()
        print("Put away a PostgreSQL connection. output df saved")
    except (Exception, psycopg2.DatabaseError) as error:
        print("Error while connecting to PostgreSQL", error)

    finally:
        if cursor:
            cursor.close()
        if ps_connection:
            put_conn_back(ps_connection)
        print("Put away a PostgreSQL connection")

    return 1


def get_mysql_cursor():
    return pymysql.connect(db=MYSQL_DBNAME,
                           user=MYSQL_USER,
                           password=MYSQL_PASSWORD,
                           host=MYSQL_HOST,
                           port=int(MYSQL_PORT))


def read_mysql(sql):
    conn = get_mysql_cursor()
    cursor = conn.cursor()
    cursor.execute(sql)
    meta = cursor.fetchall()
    columns = [desc[0] for desc in cursor.description]
    df = pd.DataFrame(meta, columns=columns)
    cursor.close()
    conn.close()
    return df


def query_by_model_id(model_id):
    return read_mysql("select * from model where id = '{}'".format(model_id)).iloc[-1]


def get_task_data_json(task_id):
    series = read_mysql("select * from task where id = '{}'".format(task_id)).iloc[-1]

    data_json = json.loads(series["data_json"])
    return data_json


def get_progress_id(model_id):
    sql = "select progress_id from model where id={} ".format(model_id)
    df = read_mysql(sql)
    return df[0][0]


def execute_mysql(sql):
    conn = get_mysql_cursor()
    cursor = conn.cursor()
    logging.info(sql)
    cursor.execute(sql)
    conn.commit()
    cursor.close()
    conn.close()


def update_record(model_id, record, table):
    update = ["{}=\"{}\"".format(k, v) for k, v in record.items()]
    update = ",".join(update)
    sql = "update {} set {} where id={} ".format(table, update, model_id)
    execute_mysql(sql)


def update_task(data_json, task_id):
    data = py_to_java(str(data_json))
    data = "'" + data + "'"
    sql = "update task set data_json={} where id={} ".format(data, task_id)
    execute_mysql(sql)


def query_column_name(table_name):
    schema, table_name = table_name.split('.')
    columns = read_gp(
        "select column_name from information_schema.columns where table_name='{}' and table_schema='{}'".format(
            table_name, schema)).values
    columns = columns['column_name'].tolist()
    return columns
